//
//  DataBaseHandler.m
//  SeeTheWorld
//
//  Created by dllo on 16/2/24.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "DataBaseHandler.h"
#import <sqlite3.h>
#import "ModelOfClickZhuanTiCell.h"
#import "ModelOfHistory.h"

@implementation DataBaseHandler

#pragma mark - 单例
+ (instancetype)shareDataBaseHandler {
    
    /** 在静态区, 只初始化一次. */
    static DataBaseHandler *dataBase = nil;
    
    if (dataBase == nil) {
        dataBase = [[DataBaseHandler alloc] init];
    }
    
    return dataBase;
}

/** 定义一个sqlite结构体(对象) */
sqlite3 *db;

#pragma mark - 打开数据库.
- (void)open {
    
    /** API: sqlite3_open */
    
    if (db != nil) {
        NSLog(@"数据库已经开启!");
        return;
    }
    
    NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"student.sqlite"];
    
    /**
     * @param filename 数据库文件的路径(注意: 类型为C字符串)
     * @param ppDb 数据库对象
     */
    int result = sqlite3_open(file.UTF8String, &db);
    
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    } else {
        NSLog(@"数据库打开失败, code = %d", result);
    }
}

#pragma mark - 创建表格.
- (void)createTable {
    
    /** API: sqlite3_exec */
    
    /** 创建SQL语句. */
    NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS lanou1130(number INTEGER PRIMARY KEY AUTOINCREMENT , name text, title text, image_url text, id text)";
    
    int result = sqlite3_exec(db, createTableSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表格成功");
    } else {
        NSLog(@"创建表格失败. code:%d", result);
    }
}

#pragma mark - history 创建表格
- (void)createTable1 {
    
    /** API: sqlite3_exec */
    
    /** 创建SQL语句. */
    NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS history(number INTEGER PRIMARY KEY AUTOINCREMENT , name text)";
    
    int result = sqlite3_exec(db, createTableSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        
    } else {
        NSLog(@"创建表格失败. code:%d", result);
    }
    
}

#pragma mark - 插入数据
- (void)insertModel:(ModelOfClickZhuanTiCell *)model {
    
    /** API: sqlite3_exec */
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT into lanou1130(name, title, image_url, id) VALUES('%@', '%@', '%@', '%@')", model.name, model.title, model.image_url, model.id];
    
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
       // NSLog(@"添加数据完成");
    } else {
        NSLog(@"添加数据失败. code:%d", result);
    }
}

#pragma mark - history 插入数据
- (void)insertModel1:(NSString *)text {
    
    /** API: sqlite3_exec */
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT into history(name) VALUES('%@')", text];
   
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        // NSLog(@"添加数据完成");
    } else {
        NSLog(@"添加数据失败. code:%d", result);
    }
}

#pragma mark - 收藏页面 查询数据
- (BOOL)selectForName:(NSString *)name {
    
    /** sql语句 */
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130 WHERE name = '%@'", name];
    
    /** API: stmt结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column. */
    
    /** 声明一个stmt对象(结构体) */
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
      //  NSLog(@"准备就绪, 查询中......");
        
        /** 每行都执行查询语句 */
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            /** 如果查询条件匹配. 通过sqlite3_column函数簇 取出值. */
            return YES;
     
        }
        
        /** 销毁对象. */
        sqlite3_finalize(stmt);
    } else {
        NSLog(@"不能正常查询. code:%d", result);
        
        /** 销毁对象. */
        sqlite3_finalize(stmt);
    }

    return NO;
}

#pragma mark - 收藏页面 查询数据
- (NSMutableArray *)shouCang {
    
    /** 存放复合条件的数据(元素是Model对象). */
    NSMutableArray *arr = [NSMutableArray array];
    
    /** sql语句 */
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130"];
    
    /** API: stmt结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column. */
    
    /** 声明一个stmt对象(结构体) */
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
     //   NSLog(@"准备就绪, 查询中......");
        
        /** 每行都执行查询语句 */
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            /** 如果查询条件匹配. 通过sqlite3_column函数簇 取出值. */
            
            /** 取出哪一列的数据值. */
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            const unsigned char *image_url = sqlite3_column_text(stmt, 3);
            const unsigned char *id = sqlite3_column_text(stmt, 4);

            
            /** 创建modele对象, 赋值之后放入数组中 */
            ModelOfClickZhuanTiCell *model = [[ModelOfClickZhuanTiCell alloc] init];
            model.name = [NSString stringWithUTF8String:(const char *) name];
            model.title = [NSString stringWithUTF8String:(const char *) title];
            model.image_url = [NSString stringWithUTF8String:(const char *) image_url];
            model.id = [NSString stringWithUTF8String:(const char *) id];
            
            [arr addObject:model];
            
        }
        
        /** 销毁对象. */
        sqlite3_finalize(stmt);
    } else {
        NSLog(@"不能正常查询. code:%d", result);
        
        /** 销毁对象. */
        sqlite3_finalize(stmt);
    }

    return arr;
}

#pragma mark - history页面 查询数据
- (NSMutableArray *)history {
    
    /** 存放复合条件的数据(元素是Model对象). */
    NSMutableArray *arr = [NSMutableArray array];
    
    /** sql语句 */
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM history"];
    
    /** API: stmt结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column. */
    
    /** 声明一个stmt对象(结构体) */
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        //   NSLog(@"准备就绪, 查询中......");
        
        /** 每行都执行查询语句 */
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            /** 如果查询条件匹配. 通过sqlite3_column函数簇 取出值. */
            
            /** 取出哪一列的数据值. */
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            
            /** 创建modele对象, 赋值之后放入数组中 */
            ModelOfHistory *model = [[ModelOfHistory alloc] init];
            
            model.text = [NSString stringWithUTF8String:(const char *) name];
            
            [arr insertObject:model atIndex:0];
            
        }
        
        /** 销毁对象. */
        sqlite3_finalize(stmt);
    } else {
        NSLog(@"不能正常查询. code:%d", result);
        
        /** 销毁对象. */
        sqlite3_finalize(stmt);
    }
    
    return arr;
}

#pragma mark - history页面 查询数据
- (BOOL)selectForHistory:(NSString *)name {
    
    /** sql语句 */
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM history WHERE name = '%@'", name];
    
    /** API: stmt结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column. */
    
    /** 声明一个stmt对象(结构体) */
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        //  NSLog(@"准备就绪, 查询中......");
        
        /** 每行都执行查询语句 */
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            /** 如果查询条件匹配. 通过sqlite3_column函数簇 取出值. */
            return YES;
            
        }
        
        /** 销毁对象. */
        sqlite3_finalize(stmt);
    } else {
        NSLog(@"不能正常查询. code:%d", result);
        
        /** 销毁对象. */
        sqlite3_finalize(stmt);
    }
    
    return NO;
}

#pragma mark - 知识点6 删除数据
- (void)deleteWithName:(NSString *)name {
    
    /** API: sqlite3_exec */
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM lanou1130 WHERE name = '%@'", name];
    
    int result = sqlite3_exec(db, deleteSQL.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        
    //    NSLog(@"删除成功");
        
    } else {
        
        NSLog(@"删除失败. code:%d", result);
    }
}

#pragma mark - 删除历史纪录
- (void)deleteHistory {
    
    /** API: sqlite3_exec */
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM history"];
    
    int result = sqlite3_exec(db, deleteSQL.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        
        // NSLog(@"删除成功");
        
    } else {
        
        NSLog(@"删除失败. code:%d", result);
    }
    
}










@end
